package com.xyz.keshe.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "news_db";
    private static final int DATABASE_VERSION = 1;

    // 表名
    public static final String TABLE_ADMIN = "admin";
    public static final String TABLE_CATEGORY = "category";
    public static final String TABLE_NEWS = "news";
    public static final String TABLE_COMMENT = "comment";
    public static final String TABLE_ANNOUNCEMENT = "announcement";

    // 通用字段
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_CREATE_TIME = "create_time";
    public static final String COLUMN_UPDATE_TIME = "update_time";

    // 管理员表字段
    public static final String COLUMN_ADMIN_USERNAME = "username";
    public static final String COLUMN_ADMIN_PASSWORD = "password";
    public static final String COLUMN_ADMIN_IS_SUPER = "is_super"; // 是否超级管理员

    // 栏目表字段
    public static final String COLUMN_CATEGORY_NAME = "name";
    public static final String COLUMN_CATEGORY_DESCRIPTION = "description";

    // 新闻表字段
    public static final String COLUMN_NEWS_TITLE = "title";
    public static final String COLUMN_NEWS_CONTENT = "content";
    public static final String COLUMN_NEWS_AUTHOR = "author";
    public static final String COLUMN_NEWS_CATEGORY_ID = "category_id";
    public static final String COLUMN_NEWS_VIEW_COUNT = "view_count";

    // 评论表字段
    public static final String COLUMN_COMMENT_CONTENT = "content";
    public static final String COLUMN_COMMENT_NEWS_ID = "news_id";
    public static final String COLUMN_COMMENT_USER_NAME = "user_name";

    // 公告表字段
    public static final String COLUMN_ANNOUNCEMENT_TITLE = "title";
    public static final String COLUMN_ANNOUNCEMENT_CONTENT = "content";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建管理员表
        String createAdminTable = "CREATE TABLE " + TABLE_ADMIN + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_ADMIN_USERNAME + " TEXT NOT NULL,"
                + COLUMN_ADMIN_PASSWORD + " TEXT NOT NULL,"
                + COLUMN_ADMIN_IS_SUPER + " INTEGER DEFAULT 0,"
                + COLUMN_CREATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                + COLUMN_UPDATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
                + ")";

        // 创建栏目表
        String createCategoryTable = "CREATE TABLE " + TABLE_CATEGORY + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_CATEGORY_NAME + " TEXT NOT NULL,"
                + COLUMN_CATEGORY_DESCRIPTION + " TEXT,"
                + COLUMN_CREATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                + COLUMN_UPDATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
                + ")";

        // 创建新闻表
        String createNewsTable = "CREATE TABLE " + TABLE_NEWS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_NEWS_TITLE + " TEXT NOT NULL,"
                + COLUMN_NEWS_CONTENT + " TEXT NOT NULL,"
                + COLUMN_NEWS_AUTHOR + " TEXT,"
                + COLUMN_NEWS_CATEGORY_ID + " INTEGER NOT NULL,"
                + COLUMN_NEWS_VIEW_COUNT + " INTEGER DEFAULT 0,"
                + COLUMN_CREATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                + COLUMN_UPDATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                + "FOREIGN KEY(" + COLUMN_NEWS_CATEGORY_ID + ") REFERENCES " + TABLE_CATEGORY + "(" + COLUMN_ID + ") ON DELETE CASCADE"
                + ")";

        // 创建评论表
        String createCommentTable = "CREATE TABLE " + TABLE_COMMENT + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_COMMENT_CONTENT + " TEXT NOT NULL,"
                + COLUMN_COMMENT_NEWS_ID + " INTEGER NOT NULL,"
                + COLUMN_COMMENT_USER_NAME + " TEXT NOT NULL,"
                + COLUMN_CREATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                + "FOREIGN KEY(" + COLUMN_COMMENT_NEWS_ID + ") REFERENCES " + TABLE_NEWS + "(" + COLUMN_ID + ") ON DELETE CASCADE"
                + ")";

        // 创建公告表
        String createAnnouncementTable = "CREATE TABLE " + TABLE_ANNOUNCEMENT + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_ANNOUNCEMENT_TITLE + " TEXT NOT NULL,"
                + COLUMN_ANNOUNCEMENT_CONTENT + " TEXT NOT NULL,"
                + COLUMN_CREATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                + COLUMN_UPDATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
                + ")";

        db.execSQL(createAdminTable);
        db.execSQL(createCategoryTable);
        db.execSQL(createNewsTable);
        db.execSQL(createCommentTable);
        db.execSQL(createAnnouncementTable);

        // 添加一个默认超级管理员账号
        ContentValues adminValues = new ContentValues();
        adminValues.put(COLUMN_ADMIN_USERNAME, "admin");
        adminValues.put(COLUMN_ADMIN_PASSWORD, "123456");
        adminValues.put(COLUMN_ADMIN_IS_SUPER, 1);
        db.insert(TABLE_ADMIN, null, adminValues);

        // 添加默认栏目
        String[] defaultCategories = {"国内", "国际", "体育", "科技", "娱乐"};
        for (String category : defaultCategories) {
            ContentValues categoryValues = new ContentValues();
            categoryValues.put(COLUMN_CATEGORY_NAME, category);
            categoryValues.put(COLUMN_CATEGORY_DESCRIPTION, category + "相关新闻");
            db.insert(TABLE_CATEGORY, null, categoryValues);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NEWS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORY);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ANNOUNCEMENT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ADMIN);
        onCreate(db);
    }
} 